package com.example.demo01.Impl.webImpl;

import com.example.demo01.Dao.webDao.SelectTreeEntityDao;
import com.example.demo01.Entity.webEntity.SelectTreeItemEntity;
import com.example.demo01.utils.JDBCPGUtils;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.lang.Nullable;
import org.springframework.stereotype.Repository;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

@Repository
public class SelectTreeEntityDaoImpl implements SelectTreeEntityDao {

    //自动装配
    @Autowired
    private JdbcTemplate jdbcTemplate;

    /**
     * 获取焦作市城市名称列表
     */
    @Override
    public List<SelectTreeItemEntity> getCityList() {
        List<SelectTreeItemEntity> cityList = new ArrayList<>();//结果集
        SelectTreeItemEntity selectTreeEntity = null;
        String sqlQueryCity = "select pac as id,name as label,geom from jiaozuo";//sql语句
        //声明变量
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        //数据库操作
        try {
            connection = JDBCPGUtils.getConnection();
            statement = connection.prepareStatement(sqlQueryCity);
            resultSet = statement.executeQuery();
            //解析结果
            while (resultSet.next()) {
                selectTreeEntity = new SelectTreeItemEntity(
                        resultSet.getString("id"),
                        resultSet.getString("label"),
                        resultSet.getString("geom"),
                        new ArrayList<>()
                );
                cityList.add(selectTreeEntity);
            }
            //添加护理等级信息
            for (int i = 0; i < cityList.size(); i++) {
                //获取第i个元素
                SelectTreeItemEntity entity = cityList.get(i);
                //添加护理等级信息到children中
                cityList.get(i).getChildren().add(new SelectTreeItemEntity(entity.getId() + "-1", "特护一级", new ArrayList<SelectTreeItemEntity>()));
                cityList.get(i).getChildren().add(new SelectTreeItemEntity(entity.getId() + "-2", "特护二级", new ArrayList<SelectTreeItemEntity>()));
                cityList.get(i).getChildren().add(new SelectTreeItemEntity(entity.getId() + "-3", "护理一级", new ArrayList<SelectTreeItemEntity>()));
                cityList.get(i).getChildren().add(new SelectTreeItemEntity(entity.getId() + "-4", "护理一级", new ArrayList<SelectTreeItemEntity>()));
                cityList.get(i).getChildren().add(new SelectTreeItemEntity(entity.getId() + "-5", "普通", new ArrayList<SelectTreeItemEntity>()));
            }
            //为结果集添加用户信息
            cityList = addUserInfoIntoCityList(cityList);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //释放资源
            JDBCPGUtils.close(resultSet, statement, connection);
        }
        //返回结果集
        return cityList;
    }

    public List<SelectTreeItemEntity> addUserInfoIntoCityList(List<SelectTreeItemEntity> cityList) {
        //获取用户结果集
        List<SelectTreeItemEntity> treeItemEntities = getUserInfoSimple();
        //临时结果集
        List<SelectTreeItemEntity> tempList1 = null;
        List<SelectTreeItemEntity> tempList2 = null;
        List<SelectTreeItemEntity> tempList3 = null;
        List<SelectTreeItemEntity> tempList4 = null;
        List<SelectTreeItemEntity> tempList5 = null;
        //临时id值
        String id = "";
        //遍历城市结果集
        for (int i = 0; i < cityList.size(); i++) {
            //实例化临时结果集
            tempList1 = new ArrayList<>();
            tempList2 = new ArrayList<>();
            tempList3 = new ArrayList<>();
            tempList4 = new ArrayList<>();
            tempList5 = new ArrayList<>();
            //临时id值
            id = cityList.get(i).getId().trim();
            //遍历用户结果集
            for (int j = 0; j < treeItemEntities.size(); j++) {
                //判断标志信息
                String nurselevel = treeItemEntities.get(j).getGeom().trim();
                //判断id值是否一致
                if (!treeItemEntities.get(j).getId().trim().substring(0, 6).equals(id)) {
                    continue;
                } else {
                    switch (nurselevel) {
                        case "特护一级": {
                            tempList1.add(treeItemEntities.get(j));
                            break;
                        }
                        case "特护二级": {
                            tempList2.add(treeItemEntities.get(j));
                            break;
                        }
                        case "护理一级": {
                            tempList3.add(treeItemEntities.get(j));
                            break;
                        }
                        case "护理二级": {
                            tempList4.add(treeItemEntities.get(j));
                            break;
                        }
                        default: {
                            tempList5.add(treeItemEntities.get(j));
                        }
                    }
                }

            }
            //分配临时列表
            cityList.get(i).getChildren().get(0).getChildren().addAll(tempList1);//cityList.get(i).getChildren()-城市  cityList.get(i).getChildren().get(0).getChildren()-特护一级
            cityList.get(i).getChildren().get(1).getChildren().addAll(tempList2);//cityList.get(i).getChildren()-城市  cityList.get(i).getChildren().get(1).getChildren()-特护二级
            cityList.get(i).getChildren().get(2).getChildren().addAll(tempList3);//cityList.get(i).getChildren()-城市  cityList.get(i).getChildren().get(2).getChildren()-护理一级
            cityList.get(i).getChildren().get(3).getChildren().addAll(tempList4);//cityList.get(i).getChildren()-城市  cityList.get(i).getChildren().get(3).getChildren()-护理一级
            cityList.get(i).getChildren().get(4).getChildren().addAll(tempList5);//cityList.get(i).getChildren()-城市  cityList.get(i).getChildren().get(4).getChildren()-普通
            //从用户列表中移除无用的信息，提高查询效率
            treeItemEntities.removeAll(tempList1);
            treeItemEntities.removeAll(tempList2);
            treeItemEntities.removeAll(tempList3);
            treeItemEntities.removeAll(tempList4);
            treeItemEntities.removeAll(tempList5);
        }
        return cityList;
    }

    @Test
    public void test1() {
        System.out.println("41080219200825063X".substring(0, 6));
    }

    /**
     * 获取所有的用户数据
     * @return 所有用户数据列表
     */
    public List<SelectTreeItemEntity> getUserInfoSimple() {
        String sqlUserinfo = "SELECT idnum as id,name as label,nurselevel as geom from userinfo1";
        return jdbcTemplate.query(sqlUserinfo, new RowMapper<SelectTreeItemEntity>() {
            @Nullable
            @Override
            public SelectTreeItemEntity mapRow(ResultSet resultSet, int i) throws SQLException {
                return new SelectTreeItemEntity(
                        resultSet.getString("id"),
                        resultSet.getString("label"),
                        resultSet.getString("geom")
                );
            }
        });
    }


}
